package com.example.yjhmatch.mapper;

import com.example.yjhmatch.entity.StatisticsDTO;
import com.yc.yjh.entity.Statistics;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 廖老师
 * @since 2023-07-21
 */
public interface StatisticsMapper extends BaseMapper<Statistics> {
    @Select("SELECT s.*," +
            "p.name as \"players.name\",p.replace as \"players.replace\",p.photo_url as \"players.photo_url\", p.team_id as \"players.team_id\", " +
            "t.name as \"teams.name\"," +
            "h1.icon as \"phero.icon\",h2.icon as \"bhero.icon\" ," +
            "h1.name as \"phero.name\",h2.name as \"bhero.name\" ," +
            "r.round as \"results.round\",r.loser_team_id as \"results.loser_team_id\",r.winner_team_id as \"results.winner_team_id\" " +
            "from statistics s " +
            "INNER JOIN players p on s.player_id=p.player_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "INNER JOIN heroes h1 on s.hero_id=h1.hero_id " +
            "INNER JOIN results r on s.results_id=r.result_id " +
            "INNER JOIN heroes h2 on s.ban_hero_id=h2.hero_id where s.match_id=#{match_id}")
    List<Statistics> selectByMatchId(@Param("match_id") String match_id);
    @Select("select SUM(kills) from statistics s " +
            "INNER JOIN results r  on s.results_id=r.result_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "where round=#{round} and name=#{name}")
    Integer selectSumKills(@Param("round") Integer round,@Param("name") String name);

    @Select("SELECT coalesce(SUM(kills),0) kills,p.name as \"players.name\",p.photo_url as \"players.photo_url\",p.replace as \"players.replace\"," +
            "t.icon as \"teams.icon\" " +
            "from statistics s " +
            "INNER JOIN players p on s.player_id=p.player_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "GROUP BY s.player_id " +
            "ORDER BY SUM(kills) DESC")
    List<Statistics> orderBySumKills();
    @Select("SELECT coalesce(SUM(assists),0) assists,p.name as \"players.name\",p.photo_url as \"players.photo_url\",p.replace as \"players.replace\"," +
            "t.icon as \"teams.icon\" " +
            "from statistics s " +
            "INNER JOIN players p on s.player_id=p.player_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "GROUP BY s.player_id " +
            "ORDER BY SUM(assists) DESC")
    List<Statistics> orderBySumAssists();
    @Select("SELECT coalesce(SUM(minions_killed),0) minions_killed,p.name as \"players.name\",p.photo_url as \"players.photo_url\",p.replace as \"players.replace\"," +
            "t.icon as \"teams.icon\" " +
            "from statistics s " +
            "INNER JOIN players p on s.player_id=p.player_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "GROUP BY s.player_id " +
            "ORDER BY SUM(minions_killed) DESC")
    List<Statistics> orderBySumMinionsKilled();
    @Select("SELECT coalesce(SUM(money),0) money,p.name as \"players.name\",p.photo_url as \"players.photo_url\",p.replace as \"players.replace\"," +
            "t.icon as \"teams.icon\" " +
            "from statistics s " +
            "INNER JOIN players p on s.player_id=p.player_id " +
            "INNER JOIN teams t on s.team_id=t.team_id " +
            "GROUP BY s.player_id " +
            "ORDER BY SUM(money) DESC")
    List<Statistics> orderBySumMoney();
    @Select("SELECT COUNT(ban_hero_id) ban_hero_id,h.name as \"bhero.name\",h.icon  as \"bhero.icon\"from statistics s " +
            "INNER JOIN heroes h on s.ban_hero_id=h.hero_id " +
            "GROUP BY ban_hero_id " +
            "ORDER BY COUNT(ban_hero_id) Desc limit 0,10")
    List<Statistics> orderBySumBanHero();
    @Select("SELECT COUNT(s.hero_id) as \"phero.hero_id\",h.name as \"phero.name\",h.icon  as \"phero.icon\"from statistics s " +
            "INNER JOIN heroes h on s.hero_id=h.hero_id " +
            "GROUP BY ban_hero_id " +
            "ORDER BY COUNT(s.hero_id) Desc limit 0,10")
    List<Statistics> orderBySumPickHero();

    @Select("select s.*,m.home_team_name as \"matches.home_team_name\" ,m.away_team_name as \"matches.away_team_name\"," +
            "m.date as \"matches.date\" ,h.icon as  \"heroes.icon\"    " +
            "from  matches m inner join  statistics s on s.match_id=m.match_id" +
            " inner join heroes h on s.hero_id=h.hero_id  where player_id=#{player_id}")
    List<Statistics> selectByPlayerId(@Param("player_id") String playerId);
}
